--    Program: 		CAPPOIMP_RECEIVING_OPTION.sql
--    Description:  Import PO RECEIVING OPTION information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         27-Jan-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
                    		                 		
   cursor cur_receiving_option is
      select * from crst_rcv_parameters;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_receiving_option loop   	
     l_err_msg_tmp := '';
     
     begin
       -- import data
       insert into RCV_PARAMETERS
       (
         ORGANIZATION_ID                 ,
         LAST_UPDATE_DATE                ,
         LAST_UPDATED_BY                 ,
         CREATION_DATE                   ,
         CREATED_BY                      ,
         LAST_UPDATED_LOGIN              ,
         QTY_RCV_TOLERANCE               ,
         QTY_RCV_EXCEPTION_CODE          ,
         ENFORCE_SHIP_TO_LOCATION_CODE   ,
         ALLOW_EXPRESS_DELIVERY_FLAG     ,
         DAYS_EARLY_RECEIPT_ALLOWED      ,
         DAYS_LATE_RECEIPT_ALLOWED       ,
         RECEIPT_DAYS_EXCEPTION_CODE     ,
         RECEIVING_ROUTING_ID            ,
         ALLOW_SUBSTITUTE_RECEIPTS_FLAG  ,
         ALLOW_UNORDERED_RECEIPTS_FLAG   ,
         BLIND_RECEIVING_FLAG            ,
         REQUEST_ID                      ,
         PROGRAM_APPLICATION_ID          ,
         PROGRAM_ID                      ,
         PROGRAM_UPDATE_DATE             ,
         RECEIVING_ACCOUNT_ID            ,
         ALLOW_CASCADE_TRANSACTIONS      ,
         GLOBAL_ATTRIBUTE1               ,
         GLOBAL_ATTRIBUTE2               ,
         GLOBAL_ATTRIBUTE3               ,
         GLOBAL_ATTRIBUTE4               ,
         GLOBAL_ATTRIBUTE5               ,
         GLOBAL_ATTRIBUTE6               ,
         GLOBAL_ATTRIBUTE7               ,
         GLOBAL_ATTRIBUTE8               ,
         GLOBAL_ATTRIBUTE9               ,
         GLOBAL_ATTRIBUTE10              ,
         GLOBAL_ATTRIBUTE11              ,
         GLOBAL_ATTRIBUTE12              ,
         GLOBAL_ATTRIBUTE13              ,
         GLOBAL_ATTRIBUTE14              ,
         GLOBAL_ATTRIBUTE15              ,
         GLOBAL_ATTRIBUTE16              ,
         GLOBAL_ATTRIBUTE17              ,
         GLOBAL_ATTRIBUTE18              ,
         GLOBAL_ATTRIBUTE19              ,
         GLOBAL_ATTRIBUTE20              ,
         GLOBAL_ATTRIBUTE_CATEGORY       ,
         RECEIPT_ASN_EXISTS_CODE         ,
         USER_DEFINED_RECEIPT_NUM_CODE   ,
         MANUAL_RECEIPT_NUM_TYPE         ,
         NEXT_RECEIPT_NUM             
       )
       values
       (
         CAPBRIMP_MAPPING.GetOrganizationMapping(cur.ORGANIZATION_ID),
         cur.LAST_UPDATE_DATE                ,
         CUR.LAST_UPDATED_BY,
         cur.CREATION_DATE                   ,
         CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
         cur.LAST_UPDATED_LOGIN              ,
         cur.QTY_RCV_TOLERANCE               ,
         cur.QTY_RCV_EXCEPTION_CODE          ,
         cur.ENFORCE_SHIP_TO_LOCATION_CODE   ,
         cur.ALLOW_EXPRESS_DELIVERY_FLAG     ,
         cur.DAYS_EARLY_RECEIPT_ALLOWED      ,
         cur.DAYS_LATE_RECEIPT_ALLOWED       ,
         cur.RECEIPT_DAYS_EXCEPTION_CODE     ,
         cur.RECEIVING_ROUTING_ID            ,
         cur.ALLOW_SUBSTITUTE_RECEIPTS_FLAG  ,
         cur.ALLOW_UNORDERED_RECEIPTS_FLAG   ,
         cur.BLIND_RECEIVING_FLAG            ,
         cur.REQUEST_ID                      ,
         cur.PROGRAM_APPLICATION_ID          ,
         cur.PROGRAM_ID                      ,
         cur.PROGRAM_UPDATE_DATE             ,
         CAPBRIMP_MAPPING.GetCCIDMapping(cur.RECEIVING_ACCOUNT_ID),
         cur.ALLOW_CASCADE_TRANSACTIONS      ,
         cur.GLOBAL_ATTRIBUTE1               ,
         cur.GLOBAL_ATTRIBUTE2               ,
         cur.GLOBAL_ATTRIBUTE3               ,
         cur.GLOBAL_ATTRIBUTE4               ,
         cur.GLOBAL_ATTRIBUTE5               ,
         cur.GLOBAL_ATTRIBUTE6               ,
         cur.GLOBAL_ATTRIBUTE7               ,
         cur.GLOBAL_ATTRIBUTE8               ,
         cur.GLOBAL_ATTRIBUTE9               ,
         cur.GLOBAL_ATTRIBUTE10              ,
         cur.GLOBAL_ATTRIBUTE11              ,
         cur.GLOBAL_ATTRIBUTE12              ,
         cur.GLOBAL_ATTRIBUTE13              ,
         cur.GLOBAL_ATTRIBUTE14              ,
         cur.GLOBAL_ATTRIBUTE15              ,
         cur.GLOBAL_ATTRIBUTE16              ,
         cur.GLOBAL_ATTRIBUTE17              ,
         cur.GLOBAL_ATTRIBUTE18              ,
         cur.GLOBAL_ATTRIBUTE19              ,
         cur.GLOBAL_ATTRIBUTE20              ,
         cur.GLOBAL_ATTRIBUTE_CATEGORY       ,
         cur.RECEIPT_ASN_EXISTS_CODE         ,
         cur.USER_DEFINED_RECEIPT_NUM_CODE   ,
         cur.MANUAL_RECEIPT_NUM_TYPE         ,
         cur.NEXT_RECEIPT_NUM        
       );  
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;
     
	 if l_err_msg_tmp IS NULL then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;  
   end loop;

   if l_rejected_count = 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPPOIMP_RECEIVING_OPTION', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
